package com.orange.tool.test;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.io.IOUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.test.context.web.WebAppConfiguration;

import com.pomelo.Application;
import com.pomelo.base.core.util.CommonUtil;
import com.pomelo.base.core.util.DbUtil;
import com.pomelo.base.core.util.StringUtil;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
@WebAppConfiguration
public class CompareZxbz {

    private static Log LOGGER = LogFactory.getLog(CompareZxbz.class);

    @Test
    public void compareZxbz() {
        try {
            String sql = "SELECT * FROM ORANGE_ZXBZ_LIST ORDER BY ORDER_ID_";
            List<Map<String, Object>> datas = DbUtil.query(sql, new Object[] {});
            sql = "SELECT TABLE_NAME FROM USER_TAB_COMMENTS WHERE TABLE_NAME LIKE 'T_ZXBZ%'";
            List<Object> hrmsTables = DbUtil.queryOnes("rs_test", sql, new Object[] {});
            List<Object> zxbzTables = new ArrayList<>();
            List<String> msgList = new ArrayList<>();
            LinkedHashMap<String, Object> result = new LinkedHashMap<>();
            for (Map<String, Object> data : datas) {
                String table = (String) data.get("TABLENAME_");
                String description = (String) data.get("DESCRIPTION_");
                String source_ = (String) data.get("SOURCE_");
                String key = table;
                if (!StringUtil.isEmpty(description)) {
                    key += "（" + description + "）";
                }
                if (!StringUtil.isEmpty(source_)) {
                    key += "（" + source_ + "）";
                }
                if (hrmsTables.contains(table)) {
                    hrmsTables.remove(table);
                    String sql1 = "SELECT * FROM " + table + " ORDER BY PX";
                    List<Map<String, Object>> rows = DbUtil.query(sql1, new Object[] {});
                    String sql2 = "SELECT * FROM " + table + " ORDER BY PX";
                    List<Map<String, Object>> rows2 = DbUtil.query("rs_test", sql2, new Object[] {});
                    compare(table, rows, rows2, result, msgList, key);
                } else {
                    zxbzTables.add(table);
                }
            }
            wirteExcel(result, hrmsTables, zxbzTables);
            LOGGER.info("标准多出的表:" + zxbzTables.size());
            LOGGER.info("人事多出的表:" + hrmsTables.size());
            LOGGER.info("差异表的表:" + msgList.size());
            for (Object table : zxbzTables) {
                LOGGER.info("执行标准多出:" + table);
            }
            for (Object table : hrmsTables) {
                LOGGER.info("人事系统多出的执行标准:" + table);
            }
            for (String msg : msgList) {
                LOGGER.info(msg);
            }
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
        }
        LOGGER.info("===============^-^结束^-^===============");
    }

    private void compare(String table, List<Map<String, Object>> rows, List<Map<String, Object>> rows2,
        LinkedHashMap<String, Object> result, List<String> msgList, String key1) throws Exception {
        Map<String, Map<String, Object>> rowsMap = CommonUtil.convertListToMap(rows, "DM");
        Map<String, Map<String, Object>> rows2Map = CommonUtil.convertListToMap(rows2, "DM");
        int exists = 0;
        int zxbzMore = 0;
        int hrmsMore = 0;
        boolean flag = true;
        List<Object[]> existList = new ArrayList<>();
        List<Object[]> hrmsList = new ArrayList<>();
        List<Object[]> zxbzList = new ArrayList<>();
        for (Iterator<Entry<String, Map<String, Object>>> iter = rowsMap.entrySet().iterator(); iter.hasNext();) {
            Entry<String, Map<String, Object>> entry = iter.next();
            Map<String, Object> zxbzData = entry.getValue();
            String key = entry.getKey();
            if (rows2Map.containsKey(key)) {
                Map<String, Object> hrmsData = entry.getValue();
                Object[] row = new Object[9];
                row[0] = zxbzData.get("DM");
                row[1] = zxbzData.get("MC");
                row[2] = zxbzData.get("LS");
                row[3] = zxbzData.get("CC");
                row[4] = hrmsData.get("DM");
                row[5] = hrmsData.get("MC");
                row[6] = hrmsData.get("LS");
                row[7] = hrmsData.get("CC");
                String[] fields = { "DM", "MC", "LS", "CC" };
                boolean equal = true;
                for (String field : fields) {
                    if (!StringUtil.toStr(zxbzData.get(field)).equals(StringUtil.toStr(hrmsData.get(field)))) {
                        equal = false;
                    }
                }
                if (equal) {
                    row[8] = "相等";
                } else {
                    row[8] = "不相等";
                }
                existList.add(row);
            } else {
                flag = false;
                zxbzMore++;
                Object[] row = new Object[9];
                row[0] = zxbzData.get("DM");
                row[1] = zxbzData.get("MC");
                row[2] = zxbzData.get("LS");
                row[3] = zxbzData.get("CC");
                row[4] = null;
                row[5] = null;
                row[6] = null;
                row[7] = null;
                row[8] = "执行标准多";
                zxbzList.add(row);
            }
        }
        for (Iterator<Entry<String, Map<String, Object>>> iter = rows2Map.entrySet().iterator(); iter.hasNext();) {
            Entry<String, Map<String, Object>> entry = iter.next();
            String key = entry.getKey();
            if (!rowsMap.containsKey(key)) {
                flag = false;
                hrmsMore++;
                Map<String, Object> hrmsData = entry.getValue();
                Object[] row = new Object[9];
                row[0] = null;
                row[1] = null;
                row[2] = null;
                row[3] = null;
                row[4] = hrmsData.get("DM");
                row[5] = hrmsData.get("MC");
                row[6] = hrmsData.get("LS");
                row[7] = hrmsData.get("CC");
                row[8] = "人事多";
                hrmsList.add(row);
            }
        }
        if (!flag) {
            String msg = String.format("表名称：%s;相同数量%d;标准多出数量%d;人事多出的数量%d.", table, exists, zxbzMore, hrmsMore);
            msgList.add(msg);
        }
        Map<String, Object> map = new HashMap<>();
        map.put("hrmsList", hrmsList);
        map.put("zxbzList", zxbzList);
        map.put("existList", existList);
        result.put(key1, map);
    }

    @SuppressWarnings({ "resource" })
    private void wirteExcel(LinkedHashMap<String, Object> result, List<Object> hrmsTables, List<Object> zxbzTables)
        throws Exception {
        File file = new File("E:\\wisedu\\高校信息标准（基础代码）规范V6.0.xlsx");
        XSSFWorkbook workBook = new XSSFWorkbook();

        // 创建样式对象
        XSSFCellStyle style = workBook.createCellStyle();
        // 设置样式对齐方式：水平\垂直居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        XSSFFont font = workBook.createFont();
        font.setFontHeightInPoints((short) 16);// 设置字体大小
        font.setBold(true);
        style.setFont(font);

        XSSFCellStyle style1 = workBook.createCellStyle();
        style1.setBorderBottom(BorderStyle.THIN);
        style1.setBorderLeft(BorderStyle.THIN);
        style1.setBorderRight(BorderStyle.THIN);
        style1.setBorderTop(BorderStyle.THIN);
        // 设定填充单色
        style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style1.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        XSSFFont font1 = workBook.createFont();
        font1.setColor(IndexedColors.WHITE.getIndex());
        font1.setBold(true);
        style1.setFont(font1);

        XSSFCellStyle style2 = workBook.createCellStyle();
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());

        XSSFCellStyle style3 = workBook.createCellStyle();
        style3.setBorderBottom(BorderStyle.THIN);
        style3.setBorderLeft(BorderStyle.THIN);
        style3.setBorderRight(BorderStyle.THIN);
        style3.setBorderTop(BorderStyle.THIN);

        // sheet 对应一个工作页
        Sheet sheet = workBook.createSheet("差异记录");
        exist(workBook, sheet, style, style1, style2, style3, result);

        // sheet 对应一个工作页
        Sheet sheet1 = workBook.createSheet("人事多出表");
        more(workBook, sheet1, hrmsTables, style, style1, style3, "rs_test");
        Sheet sheet2 = workBook.createSheet("执行标准多出表");
        more(workBook, sheet2, zxbzTables, style, style1, style3, null);
        FileOutputStream os = new FileOutputStream(file);
        os.flush();
        workBook.write(os);
        IOUtils.closeQuietly(os);
    }

    @SuppressWarnings("unchecked")
    private void exist(XSSFWorkbook workBook, Sheet sheet, XSSFCellStyle style, XSSFCellStyle style1,
        XSSFCellStyle style2, XSSFCellStyle style3, LinkedHashMap<String, Object> result) throws Exception {
        int i = 0;
        for (Iterator<Entry<String, Object>> iter = result.entrySet().iterator(); iter.hasNext();) {
            Entry<String, Object> entry = iter.next();
            {
                // 设置字典表表头
                Row row = sheet.createRow(i);
                String title = entry.getKey();
                for (int j = 0; j < 9; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(style);
                }
                Cell cell = row.createCell(0);
                cell.setCellValue(title);
                cell.setCellStyle(style);
                // 指定合并开始行、合并结束行 合并开始列、合并结束列
                CellRangeAddress rangeAddress = new CellRangeAddress(i, i, 0, 8);
                // 添加要合并地址到表格
                sheet.addMergedRegion(rangeAddress);
                i++;
            }
            {
                // 设表置头
                Row row = sheet.createRow(i);
                String[] titles = { "代码", "名称", "隶属", "层次", "代码", "名称", "隶属", "层次", "对比结果" };
                for (int j = 0; j < titles.length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(titles[j]);
                    cell.setCellStyle(style1);
                }
                i++;
            }
            Map<String, Object> value = (Map<String, Object>) entry.getValue();
            {
                // 设置相等的内容
                List<Object[]> existList = (List<Object[]>) value.get("existList");
                for (Object[] object : existList) {
                    Row row = sheet.createRow(i);

                    for (int j = 0; j < 9; j++) {
                        Cell cell = row.createCell(j);
                        Object obj = object[j];
                        if (obj != null) {
                            cell.setCellValue(StringUtil.toStr(object[j]));
                        }
                        if ("不相等".equals(object[8])) {
                            cell.setCellStyle(style2);
                        } else {
                            cell.setCellStyle(style3);
                        }
                    }
                    i++;
                }
            }
            {
                List<Object[]> zxbzList = (List<Object[]>) value.get("zxbzList");
                for (Object[] object : zxbzList) {
                    Row row = sheet.createRow(i);
                    for (int j = 0; j < 9; j++) {
                        Cell cell = row.createCell(j);
                        Object obj = object[j];
                        if (obj != null) {
                            cell.setCellValue(StringUtil.toStr(object[j]));
                        }
                        cell.setCellStyle(style3);
                    }
                    i++;
                }
            }
            {
                List<Object[]> hrmsList = (List<Object[]>) value.get("hrmsList");
                for (Object[] object : hrmsList) {
                    Row row = sheet.createRow(i);
                    for (int j = 0; j < 9; j++) {
                        Cell cell = row.createCell(j);
                        Object obj = object[j];
                        if (obj != null) {
                            cell.setCellValue(StringUtil.toStr(object[j]));
                        }
                        cell.setCellStyle(style3);
                    }
                    i++;
                }
            }
            {
                // 指定合并开始行、合并结束行 合并开始列、合并结束列
                CellRangeAddress rangeAddress = new CellRangeAddress(i, i, 0, 8);
                // 添加要合并地址到表格
                sheet.addMergedRegion(rangeAddress);
                i++;
            }
        }
    }

    private void more(XSSFWorkbook workBook, Sheet sheet, List<Object> tables, XSSFCellStyle style,
        XSSFCellStyle style1, XSSFCellStyle style3, String dsId) throws Exception {
        // sheet 对应一个工作页
        int i = 0;
        for (Object table : tables) {
            {
                // 设置字典表表头
                Row row = sheet.createRow(i);
                for (int j = 0; j < 4; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(style);
                }
                Cell cell = row.createCell(0);
                cell.setCellValue((String) table);
                cell.setCellStyle(style);
                // 指定合并开始行、合并结束行 合并开始列、合并结束列
                CellRangeAddress rangeAddress = new CellRangeAddress(i, i, 0, 3);
                // 添加要合并地址到表格
                sheet.addMergedRegion(rangeAddress);
                i++;
            }
            {
                // 设表置头
                Row row = sheet.createRow(i);
                String[] titles = { "代码", "名称", "隶属", "层次" };
                for (int j = 0; j < titles.length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(titles[j]);
                    cell.setCellStyle(style1);
                }
                i++;
            }
            String sql1 = "SELECT * FROM " + table + " ORDER BY PX";
            List<Map<String, Object>> datas = DbUtil.query(dsId, sql1, new Object[] {});
            for (Map<String, Object> data : datas) {
                Row row = sheet.createRow(i);
                Cell cell = row.createCell(0);
                if (!StringUtil.isEmpty(data.get("DM"))) {
                    cell.setCellValue(StringUtil.toStr(data.get("DM")));
                }
                cell.setCellStyle(style3);
                Cell cell1 = row.createCell(1);
                if (!StringUtil.isEmpty(data.get("MC"))) {
                    cell1.setCellValue(StringUtil.toStr(data.get("MC")));
                }
                cell1.setCellStyle(style3);
                Cell cell2 = row.createCell(2);
                if (!StringUtil.isEmpty(data.get("LS"))) {
                    cell2.setCellValue(StringUtil.toStr(data.get("LS")));
                }
                cell2.setCellStyle(style3);
                Cell cell3 = row.createCell(3);
                if (!StringUtil.isEmpty(data.get("CC"))) {
                    cell3.setCellValue(StringUtil.toStr(data.get("CC")));
                }
                cell3.setCellStyle(style3);
                i++;
            }
        }
    }

}
